﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using control.Biz;
using control;

namespace control.Dal
{
    class DalSach
    {

        internal static List<control.Biz.BizSach> GetAll()
        {
            var saches = from s in DalCommon.SachDB.Saches
                         orderby s.NgayCapNhat descending
                         select new BizSach(s.IDSach, s.MaSach, s.TenSach, s.TacGia, (int)s.NamXuatBan, (float)s.GiaSach, (DateTime)s.NgayCapNhat, new BizChuDe(s.ChuDe.IDChuDe, s.ChuDe.TenChuDe), s.Hinh,s.MoTa, (bool)s.IsEnable);
            return saches.ToList<BizSach>();
        }

        internal static int Insert(BizSach _sach)
        {

            Sach s = new Sach();
            s.MaSach = _sach.MaSach;
            s.TenSach = _sach.TenSach;
            s.IDChuDe = _sach.ChuDe.MaChuDe;
            s.TacGia = _sach.TacGia;
            s.NamXuatBan = _sach.NamXB;
            s.GiaSach = _sach.GiaSach;
            s.NgayCapNhat = _sach.NgayCapNhat;
            s.Hinh = _sach.Hinh;
            s.MoTa = _sach.MoTa;
            s.IsEnable = _sach.IsEnable;
            DalCommon.SachDB.Saches.InsertOnSubmit(s);
            DalCommon.SachDB.SubmitChanges();
            return s.IDSach;
            
        }

        internal static void Update(BizSach _sach)
        {
            
            Sach s= DalCommon.SachDB.Saches.Single(r => r.IDSach == _sach.IdSach);
            if (s!=null)
            {
                s.MaSach = _sach.MaSach;
                s.TenSach = _sach.TenSach;
                s.IDChuDe = _sach.ChuDe.MaChuDe;
                s.TacGia = _sach.TacGia;
                s.NamXuatBan = _sach.NamXB;
                s.GiaSach = _sach.GiaSach;
                s.NgayCapNhat = _sach.NgayCapNhat;
                s.Hinh = _sach.Hinh;
                s.MoTa = _sach.MoTa;
                s.IsEnable = _sach.IsEnable;
                DalCommon.SachDB.SubmitChanges();
            }
        }

        internal static void Delete(BizSach _sach)
        {
            Sach os = DalCommon.SachDB.Saches.Single(s => s.IDSach == _sach.IdSach);
            DalCommon.SachDB.Saches.DeleteOnSubmit(os);
            DalCommon.SachDB.SubmitChanges();

        }

        internal static BizSach GetSachByID(int _ID)
        {
            var saches = from s in DalCommon.SachDB.Saches
                         where s.IDSach==_ID
                         select new BizSach(s.IDSach, s.MaSach, s.TenSach, s.TacGia, (int)s.NamXuatBan, (float)s.GiaSach, (DateTime)s.NgayCapNhat, new BizChuDe(s.ChuDe.IDChuDe, s.ChuDe.TenChuDe), s.Hinh, s.MoTa, (bool)s.IsEnable);
            return saches.FirstOrDefault<BizSach>();
        }

        internal static List<BizSach> GetNewSach()
        {
            var saches = from s in DalCommon.SachDB.Saches
                         where  s.IsEnable == true
                         orderby s.NgayCapNhat descending
                         select new BizSach(s.IDSach, s.MaSach, s.TenSach, s.TacGia, (int)s.NamXuatBan, (float)s.GiaSach, (DateTime)s.NgayCapNhat, new BizChuDe(s.ChuDe.IDChuDe, s.ChuDe.TenChuDe), s.Hinh, s.MoTa, (bool)s.IsEnable);
            return saches.ToList<BizSach>();
        }

        internal static List<BizSach> GetRandom()
        {
            var saches = from s in DalCommon.SachDB.Saches
                         where s.IsEnable==true
                         orderby s.NgayCapNhat descending
                         select new BizSach(s.IDSach, s.MaSach, s.TenSach, s.TacGia, (int)s.NamXuatBan, (float)s.GiaSach, (DateTime)s.NgayCapNhat, new BizChuDe(s.ChuDe.IDChuDe, s.ChuDe.TenChuDe), s.Hinh, s.MoTa, (bool)s.IsEnable);
            List<BizSach> listsach = saches.ToList<BizSach>();
            List<BizSach> rdsaches = new List<BizSach>();
            Random ran = new Random();
            for (int i = 0; i < 3; i++)
            {
                rdsaches.Add(listsach[ran.Next(listsach.Count())]);
            }
            return rdsaches;
        }

        internal static List<BizSach> GetSachByIDLoai(int _MaCD)
        {
            var saches = from s in DalCommon.SachDB.Saches
                         where s.IDChuDe == _MaCD && s.IsEnable == true
                         orderby s.NgayCapNhat descending
                         select new BizSach(s.IDSach, s.MaSach, s.TenSach, s.TacGia, (int)s.NamXuatBan, (float)s.GiaSach, (DateTime)s.NgayCapNhat, new BizChuDe(s.ChuDe.IDChuDe, s.ChuDe.TenChuDe), s.Hinh, s.MoTa, (bool)s.IsEnable);
            return saches.ToList<BizSach>();
        }

        internal static List<BizSach> Find(string _TenSach, int _IDChuDe)
        {
            if (_IDChuDe == 0)
            {
                var saches = from s in DalCommon.SachDB.Saches
                             where s.TenSach.Contains(_TenSach)&&s.IsEnable==true
                             orderby s.NgayCapNhat descending
                             select new BizSach(s.IDSach, s.MaSach, s.TenSach, s.TacGia, (int)s.NamXuatBan, (float)s.GiaSach, (DateTime)s.NgayCapNhat, new BizChuDe(s.ChuDe.IDChuDe, s.ChuDe.TenChuDe), s.Hinh, s.MoTa, (bool)s.IsEnable);
                return saches.ToList<BizSach>();
            }
            else
            {
                var saches = from s in DalCommon.SachDB.Saches
                             where s.TenSach.Contains(_TenSach)&&s.IDChuDe==_IDChuDe&&s.IsEnable==true
                             orderby s.NgayCapNhat descending
                             select new BizSach(s.IDSach, s.MaSach, s.TenSach, s.TacGia, (int)s.NamXuatBan, (float)s.GiaSach, (DateTime)s.NgayCapNhat, new BizChuDe(s.ChuDe.IDChuDe, s.ChuDe.TenChuDe), s.Hinh, s.MoTa, (bool)s.IsEnable);
                return saches.ToList<BizSach>();
            }
        }

        internal static List<BizSach> GetTopSeller()
        {
            
            Sach_WebDataContext db = DalCommon.SachDB;
            if (db.ChiTietPhieuXuats.ToList<ChiTietPhieuXuat>().Count > 2)
            {
                var saches = from ctpx in db.ChiTietPhieuXuats
                             group ctpx by ctpx.IDSach into ss
                             let seller = ss.Sum(s => s.SoLuong)
                             orderby seller descending
                             select BizSach.GetSachByID((int)ss.Key);

                return saches.ToList<BizSach>();
            }
            else
                return GetNewSach();
                        
        }

        internal static List<BizSach> Find(string sach, int IDchude, int giatu, int giaden, bool dangban)
        {
            if (IDchude != 0)
            {
                var saches = from s in DalCommon.SachDB.Saches
                             where (s.MaSach.Contains(sach) || s.TenSach.Contains(sach))
                             && s.ChuDe.IDChuDe == IDchude
                             && s.GiaSach >= giatu
                             && s.GiaSach <= giaden
                             && s.IsEnable == dangban
                             orderby s.NgayCapNhat descending
                             select new BizSach(s.IDSach, s.MaSach, s.TenSach, s.TacGia, (int)s.NamXuatBan, (float)s.GiaSach, (DateTime)s.NgayCapNhat, new BizChuDe(s.ChuDe.IDChuDe, s.ChuDe.TenChuDe), s.Hinh, s.MoTa, (bool)s.IsEnable);
                return saches.ToList<BizSach>();
            }
            else
            {
                var saches = from s in DalCommon.SachDB.Saches
                            where (s.MaSach.Contains(sach) || s.TenSach.Contains(sach))

                            && s.GiaSach >= giatu
                            && s.GiaSach <= giaden
                            && s.IsEnable == dangban
                            orderby s.NgayCapNhat descending
                            select new BizSach(s.IDSach, s.MaSach, s.TenSach, s.TacGia, (int)s.NamXuatBan, (float)s.GiaSach, (DateTime)s.NgayCapNhat, new BizChuDe(s.ChuDe.IDChuDe, s.ChuDe.TenChuDe), s.Hinh, s.MoTa, (bool)s.IsEnable);
                return saches.ToList<BizSach>();
            }
        }
        //thong ke
        internal static List<BizSach> XemTKTheoChuDe(int idChuDe,DateTime datetp_Batdau,DateTime dateTP_ngayKT)
        {
            List<BizSach> result = new List<BizSach>();
            if (idChuDe == 0)
            {
                var temp = from sach in DalCommon.SachDB.Saches
                           from ctpx in DalCommon.SachDB.ChiTietPhieuXuats
                           from px in DalCommon.SachDB.PhieuXuatHangs
                           where px.IDPhieuXuat == ctpx.IDPhieuXuat && ctpx.IDSach == sach.IDSach && px.NgayXuat >= datetp_Batdau&& px.NgayXuat <= dateTP_ngayKT
                           select new { px.IDPhieuXuat, sach.IDSach, ctpx.SoLuong };
                var sps = from sp in temp
                          group sp by sp.IDSach into p
                          select new { IDSach = p.Key, SoLuong = p.Sum(k => k.SoLuong) };

                foreach (var s in sps)
                {

                    Sach sach = DalCommon.SachDB.Saches.Single(p=>p.IDSach==s.IDSach);
                    BizSach newBizSach = new BizSach(sach.IDSach, sach.MaSach, sach.TenSach, sach.TacGia, (int)sach.NamXuatBan, (float)sach.GiaSach, (DateTime)sach.NgayCapNhat, sach.Hinh, (bool)sach.IsEnable, sach.MoTa, new BizChuDe(sach.ChuDe.IDChuDe, sach.ChuDe.TenChuDe), (int)s.SoLuong);
                    result.Add(newBizSach);

                }
            }
            else
            {
                var temp = from sach in DalCommon.SachDB.Saches
                           from ctpx in DalCommon.SachDB.ChiTietPhieuXuats
                           from px in DalCommon.SachDB.PhieuXuatHangs
                           where px.IDPhieuXuat == ctpx.IDPhieuXuat && ctpx.IDSach == sach.IDSach && px.NgayXuat >= datetp_Batdau && px.NgayXuat <= dateTP_ngayKT && sach.IDChuDe == idChuDe
                           select new { px.IDPhieuXuat, sach.IDSach, ctpx.SoLuong };
                var sps = from sp in temp
                          group sp by sp.IDSach into p
                          select new { IDSach = p.Key, SoLuong = p.Sum(k => k.SoLuong) };

                foreach (var s in sps)
                {

                    Sach sach = DalCommon.SachDB.Saches.Single(p => p.IDSach == s.IDSach);
                    BizSach newBizSach = new BizSach(sach.IDSach, sach.MaSach, sach.TenSach, sach.TacGia, (int)sach.NamXuatBan, (float)sach.GiaSach, (DateTime)sach.NgayCapNhat, sach.Hinh, (bool)sach.IsEnable, sach.MoTa, new BizChuDe(sach.ChuDe.IDChuDe, sach.ChuDe.TenChuDe), (int)s.SoLuong);
                    result.Add(newBizSach);

                }
            }

            return result;
        }





        //Thống kê tất cả theo chủ đề
        internal static List<BizSach> XemTKTatCaChuDe(DateTime datetp_Batdau, DateTime dateTP_ngayKT)
        {
            List<BizSach> result = new List<BizSach>();
            var temp = from sach in DalCommon.SachDB.Saches
                       from ctpx in DalCommon.SachDB.ChiTietPhieuXuats
                       from px in DalCommon.SachDB.PhieuXuatHangs
                       where px.IDPhieuXuat == ctpx.IDPhieuXuat && ctpx.IDSach == sach.IDSach && px.NgayXuat >= datetp_Batdau && px.NgayXuat <= dateTP_ngayKT
                       select new { px.IDPhieuXuat, sach.IDSach, ctpx.SoLuong };

            var sps = from sp in temp
                      group sp by sp.IDSach into p
                      select new { IDSach = p.Key, SoLuong = p.Sum(k => k.SoLuong) };

            foreach (var s in sps)
            {
                Sach sach = DalCommon.SachDB.Saches.Single(p => p.IDSach == s.IDSach);
                BizSach newBizSach = new BizSach(sach.IDSach, sach.MaSach, sach.TenSach, sach.TacGia, (int)sach.NamXuatBan, (float)sach.GiaSach, (DateTime)sach.NgayCapNhat, sach.Hinh, (bool)sach.IsEnable, sach.MoTa, new BizChuDe(sach.ChuDe.IDChuDe, sach.ChuDe.TenChuDe), (int)s.SoLuong);
                result.Add(newBizSach);

            }

            return result;

        }





        



        // Xem thống kê theo số lượng 

        internal static List<BizSach> XemThongKe(int soLuongLH, int soLuongNH,DateTime datetp_Batdau, DateTime dateTP_ngayKT)
        {

            List<BizSach> result = new List<BizSach>();
            var temp = from sach in DalCommon.SachDB.Saches
                       from ctpx in DalCommon.SachDB.ChiTietPhieuXuats
                       from px in DalCommon.SachDB.PhieuXuatHangs
                       where px.IDPhieuXuat == ctpx.IDPhieuXuat && ctpx.IDSach == sach.IDSach && px.NgayXuat >= datetp_Batdau && px.NgayXuat <= dateTP_ngayKT
                       select new { px.IDPhieuXuat, sach.IDSach, ctpx.SoLuong };
            int count1 = temp.Count();
            var sps = from sp in temp
                      group sp by sp.IDSach into p
                      select new { IDSach = p.Key, SoLuong = p.Sum(k => k.SoLuong) };

            int count = sps.Count();
            try
            {
                if (soLuongNH == 0)
                {
                    foreach (var s in sps)
                    {
                        if (s.SoLuong >= soLuongLH)
                        {
                            Sach sach = DalCommon.SachDB.Saches.Single(p => p.IDSach == s.IDSach);
                            BizSach newBizSach = new BizSach(sach.IDSach, sach.MaSach, sach.TenSach, sach.TacGia, (int)sach.NamXuatBan, (float)sach.GiaSach, (DateTime)sach.NgayCapNhat, sach.Hinh, (bool)sach.IsEnable, sach.MoTa, new BizChuDe(sach.ChuDe.IDChuDe, sach.ChuDe.TenChuDe), (int)s.SoLuong);
                            result.Add(newBizSach);
                        }
                    }
                }
                else
                {
                    foreach (var s in sps)
                    {
                        if (s.SoLuong >= soLuongLH && s.SoLuong <= soLuongNH)
                        {
                            Sach sach = DalCommon.SachDB.Saches.Single(p => p.IDSach == s.IDSach);
                            BizSach newBizSach = new BizSach(sach.IDSach, sach.MaSach, sach.TenSach, sach.TacGia, (int)sach.NamXuatBan, (float)sach.GiaSach, (DateTime)sach.NgayCapNhat, sach.Hinh, (bool)sach.IsEnable, sach.MoTa, new BizChuDe(sach.ChuDe.IDChuDe, sach.ChuDe.TenChuDe), (int)s.SoLuong);
                            result.Add(newBizSach);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }



            return result;
        }

    }
}
